[小ネタ]S3バケットを出力先に設定しているRedshift監査ログをRedshift Spectrumで分析しようとして少しハマったこと

どーも、データアナリティクス事業本部コンサルティングチームのsutoです。

Redshift監査ログを勉強しているなかで、なにも考えずログ用の外部テーブルを２種類（CnnectionlogとUserActivitylog）を用意してクエリしたところ、うまくデータが取得できないなーとなったので備忘録としてブログにします。

原因はとてもシンプル

Redshift Spectrumで分析できるように、 UserActivitylog と Connectionlog の外部テーブルを以下のように作成していました。

• UserActivityLog

CREATE EXTERNAL TABLE auditlogschema.user_activity_log(
    logrecord varchar(max)
)
 ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/Prefix/AWSLogs/your_account_id/redshift/ap-northeast-1/'
;

• ConnectionLog

CREATE EXTERNAL TABLE auditlogschema.connections_log(
  event varchar(60),  recordtime varchar(60), 
  remotehost varchar(60),  remoteport varchar(60), 
  pid int,  dbname varchar(60), 
  username varchar(60),  authmethod varchar(60), 
  duration int,  sslversion varchar(60), 
  sslcipher varchar(150),  mtu int, 
  sslcompression varchar(70),  sslexpansion varchar(70), 
  iamauthguid varchar(50),  application_name varchar(300))
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/Prefix/AWSLogs/your_account_id/redshift/ap-northeast-1/'
;

で、クエリを実行して確認したら、以下キャプチャのように

「 UserActivitylog のデータを取得したいのにレコードが実際のファイルの中身と違う。」

というかこれ、Connectionlogのレコードじゃん！

ということでS3バケットの方を見てみると

すべての種類のログファイルが同一プレフィクスに保存されています。もちろんこのような形式でデータを保存されたら、テーブル作成してもまともに分析できないですね。

ということで、いたって単純な原因でした。

RedshiftのProvisionedクラスターでは、監査ログの出力先にS3バケットとCloudWatch Logsのどちらかを選択することができ、S3バケットを選択した場合、1つのバケットとプレフィックスを設定できるが、ログの種別ごとに出力先を設定することは出来ませんでした。

そこで次項の手順に示すように、「テーブル定義を作成する際、Exclude patternを記載」してロードするファイルをフィルタリングさせましょう。

解決方法

前提

• provisioned Redshiftクラスターが作成済である
  • パラメータグループでは「enable_user_activity_logging：true」が設定済である
  • 監査ログが有効化に設定されている
• Redshift監査ログ用のS3バケットが作成済である
  • バケットポリシーに関する参考記事

• RedshiftクラスターにアタッチしているIAMロールに、関連する以下ポリシーがある
  • Redshift Spectrum用のアクセス許可
  • 監査ログ用S3バケットへのアクセス許可

監査ログ用の外部スキーマ、テーブル作成

• Redshiftにログインする（CREATE DATABASEやCREATE SCHEMAが可能なユーザーでログイン）
• Redshift監査ログ用の外部データベース(audit_logs)と外部スキーマ(auditlogschema)を作成
  • （your_account_id、AmazonRedshiftRole-arnをそれぞれ変更してお使いください）

CREATE EXTERNAL SCHEMA auditlogschema
FROM data catalog
DATABASE audit_logs
iam_role 'AmazonRedshiftRole-arn'
REGION 'ap-northeast-1'
CREATE EXTERNAL DATABASE if not exists;

• Connectionログ用の外部テーブルを作成
  • （bucket_name、your_account_id、Prefixをそれぞれ変更してお使いください）

      CREATE EXTERNAL TABLE auditlogschema.connections_log(
        event varchar(60),  recordtime varchar(60), 
        remotehost varchar(60),  remoteport varchar(60), 
        pid int,  dbname varchar(60), 
        username varchar(60),  authmethod varchar(60), 
        duration int,  sslversion varchar(60), 
        sslcipher varchar(150),  mtu int, 
        sslcompression varchar(70),  sslexpansion varchar(70), 
        iamauthguid varchar(50),  application_name varchar(300))
      ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY '|' 
      STORED AS INPUTFORMAT 
        'org.apache.hadoop.mapred.TextInputFormat' 
      OUTPUTFORMAT 
        'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
      LOCATION 's3://bucket_name/Prefix/AWSLogs/your_account_id/redshift/ap-northeast-1/'
      ;
      

  • TABLE PROPERTIESのexclusionsで、同じ場所に出力されてしまう userlog と useractivitylog のファイルを除外するようにしています
  • 【2023/1/23訂正】のちの検証でTABLE PROPERTIESのexclusionsはRedshift spectrumでは効果無かったので、削除しました。後述のWhere句でフィルタリングしてください。
• Useractivityログ用の外部テーブルを作成

      CREATE EXTERNAL TABLE auditlogschema.user_activity_log(
          logrecord varchar(max)
      )
       ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY '|' 
      STORED AS INPUTFORMAT 
        'org.apache.hadoop.mapred.TextInputFormat' 
      OUTPUTFORMAT 
        'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
      LOCATION 's3://bucket_name/Prefix/AWSLogs/your_account_id/redshift/ap-northeast-1/'
      ;
      

  • こちらの同様にTABLE PROPERTIESのexclusionsで userlog と connectionlog のファイルを除外しています
  • 【2023/1/23訂正】のちの検証でTABLE PROPERTIESのexclusionsはRedshift spectrumでは効果無かったので、削除しました。後述のWhere句でフィルタリングしてください。
• 【2023/1/23訂正】上記の修正に伴い、selectでクエリする場合は以下のようにWhereをつけて実行をお願いします。

-- Useractivity logの取得
SELECT * FROM "dev"."auditlogschema"."user_activity_log" WHERE "$path" like '%useractivitylog%';

-- Connection logの取得
SELECT * FROM "dev"."auditlogschema"."connections_log" WHERE "$path" like '%connectionlog%';

• 実際にuser_activity_logを確認してみると、問題なくデータが取得できていました（Redshiftクエリエディタv2使用）

• また、connections_logを実際にクエリしてみると以下のような表示になり問題なしです。

参考リンク